package org.bugjail.dataaccess.jdbc.objects;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import org.bugjail.data.History;
import org.bugjail.data.Project;
import org.bugjail.data.TestDesign;
import org.bugjail.data.TestRound;
import org.bugjail.dataaccess.DAOFactory;
import org.bugjail.dataaccess.jdbc.connection.QueryException;
import org.bugjail.dataaccess.jdbc.connection.QueryManager;
import org.bugjail.dataaccess.objects.ProjectDAO;
import org.bugjail.dataaccess.objects.TestDesignDAO;

/**
 * @author Felipe Melo
 * 
 */
class JDBCTestDesignDAO implements TestDesignDAO {

	private final String PROPERTIES_FILE = "/org/bugjail/dataaccess/jdbc/properties/testdesigndao.properties";

	private final String PROPERTY_INSERT = "insert";
	//private final String PROPERTY_INSERT_TESTDESIGN_TERM_ASSOCIATION = "insert.testdesigntermassociation";
	private final String PROPERTY_INSERT_TESTDESIGN_HISTORY_ASSOCIATION = "insert.testdesignhistoryassociation";

	private final String PROPERTY_UPDATE = "update";

	private final String PROPERTY_DELETE = "delete";

	private final String PROPERTY_RETRIEVE_BY_ID = "retrieve.byid";
	private final String PROPERTY_RETRIEVE_ALL = "retrieve.all";
	private final String PROPERTY_RETRIEVE_BY_NAME = "retrieve.byname";
	private final String PROPERTY_RETRIEVE_BY_PROJECT = "retrieve.byproject";
	private final String PROPERTY_RETRIEVE_BY_PROJECT_NOT_ON_ROUND = "retrieve.byproject.notonround";

	@Override
	public List<TestDesign> retrieveTestDesignsOfProject(Project project) {
		List<TestDesign> testDesigns = new LinkedList<TestDesign>();
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_RETRIEVE_BY_PROJECT);
				statement.setInt(1, project.getId());
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					testDesigns.add(extractTestDesignFromResultSet(rs,
							queryManager));
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return testDesigns;
	}

	@Override
	public void create(TestDesign element) {
		QueryManager queryManager = null;
		//List<Integer> termGenKeys = new LinkedList<Integer>();
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_INSERT);
				statement.setString(1, element.getName());
				statement.setString(2, element.getDescription());
				statement.setInt(3, element.getProject().getId());
				statement.executeUpdate();
				//ResultSet rs = statement.getGeneratedKeys();
				//int testDesignKey = 0;
				//if (rs.next()) {
				//	testDesignKey = rs.getInt(1);
				//}

				//TermDAO termDao = JDBCDAOFactory.getDAOFactory().getTermDAO();
				//for (Term term : element.getTerms()) {
				//	termGenKeys.add(termDao.createAndReturnGeneratedKeys(term));
				//}

				//for (int i = 0; i < termGenKeys.size(); i++) {
				//	this.createTestDesignTermAssociation(testDesignKey,
				//			termGenKeys.get(i), queryManager);
				//}

				QueryManager.commit(queryManager);

			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

	@Override
	public void delete(TestDesign element) {
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_DELETE);
				statement.setInt(1, element.getId());
				statement.executeUpdate();
				QueryManager.commit(queryManager);
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

	@Override
	public List<TestDesign> retrieveAll() {
		List<TestDesign> testDesigns = new LinkedList<TestDesign>();
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_RETRIEVE_ALL);
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					testDesigns.add(extractTestDesignFromResultSet(rs,
							queryManager));
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return testDesigns;
	}

	@Override
	public TestDesign retrieveById(int id) {
		TestDesign testDesign = new TestDesign();
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_RETRIEVE_BY_ID);
				statement.setInt(1, id);
				ResultSet rs = statement.executeQuery();
				if (rs.next()) {
					testDesign = extractTestDesignFromResultSet(rs,
							queryManager);
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return testDesign;
	}

	@Override
	public List<TestDesign> retrieveByName(String name) {
		name = "%" + name + "%";
		List<TestDesign> testDesigns = new LinkedList<TestDesign>();
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_RETRIEVE_BY_NAME);
				statement.setString(1, name);
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					testDesigns.add(extractTestDesignFromResultSet(rs,
							queryManager));
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return testDesigns;
	}

	@Override
	public void update(TestDesign element) {
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_UPDATE);
				statement.setString(1, element.getName());
				statement.setString(2, element.getDescription());
				statement.setInt(3, element.getProject().getId());
				statement.setInt(4, element.getId());
				statement.executeUpdate();

				QueryManager.commit(queryManager);
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

	private TestDesign extractTestDesignFromResultSet(ResultSet rs,
			QueryManager queryManager) throws SQLException {
		TestDesign testDesign = new TestDesign();
		int id = rs.getInt("id");
		testDesign.setId(id);

		ProjectDAO projectDao = DAOFactory.getDAOFactory().getProjectDAO();
		testDesign.setProject(projectDao
				.retrieveById(rs.getInt("project_id")));

		testDesign.setName(rs.getString("name"));
		testDesign.setDescription(rs.getString("description"));

		//TermDAO termDao = JDBCDAOFactory.getDAOFactory().getTermDAO();
		//testDesign.setTerms(termDao.retrieveTermsByTestDesignId(id));

		return testDesign;
	}

	@Override
	public void createTestDesignHistoryAssociation(int testDesignId,
			History element) {
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_INSERT_TESTDESIGN_HISTORY_ASSOCIATION);
				statement.setInt(1, testDesignId);
				statement.setInt(2, element.getAuthor().getId());
				statement.setString(3, element.getVersion());
				statement.executeUpdate();

				QueryManager.commit(queryManager);
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

	@Override
	public List<TestDesign> retrieveTestDesignsOfProjectNotOnRound(
			Project project, TestRound testRound) {
		List<TestDesign> testDesigns = new LinkedList<TestDesign>();
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_RETRIEVE_BY_PROJECT_NOT_ON_ROUND);
				statement.setInt(1, project.getId());
				statement.setInt(2, testRound.getId());
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					testDesigns.add(extractTestDesignFromResultSet(rs,
							queryManager));
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return testDesigns;
	}

	/*private void createTestDesignTermAssociation(int testDesignId, int termId,
			QueryManager queryManager) throws SQLException {
		PreparedStatement statement = queryManager
				.getPreparedStatement(PROPERTY_INSERT_TESTDESIGN_TERM_ASSOCIATION);
		statement.setInt(1, testDesignId);
		statement.setInt(2, termId);
		statement.executeUpdate();
	}*/
}